﻿using System;
using System.Collections.Generic;
using ChBusiness.Struct;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChBusiness.CommonBusiness;
using WF_Business;

namespace ChBusiness.User
{
    /// <summary>
    /// 用户权限申请
    /// </summary>
    public class UserRightRequest
    {
        Database dbGloab = BasicOperate.CreateNetOfficeDataBase();

        /// <summary>
        /// 根据用户获取流程权限
        /// </summary>
        /// <param name="strUserid">用户id</param>
        /// <returns></returns>
        public DataTable GetAllRightByUser(string strUserid)
        {
            string strSql = @"select id,userid,serial_name,notes,serial_time,is_pass,flowtype,
                            (case when is_pass=1 then '已通过'else '未通过' end) pass
                             from sys_user_serial where userid = :userid";//upper(:userid)

            DbCommand cmdTemp = dbGloab.GetSqlStringCommand(strSql);
            dbGloab.AddInParameter(cmdTemp, "userid", DbType.String, strUserid);

            DataTable dtTemp = dbGloab.ExecuteDataSet(cmdTemp).Tables[0];
            return dtTemp;
        }

        /// <summary>
        /// 保存流程权限信息
        /// </summary>
        /// <param name="strUserId">用户ID</param>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strFlowType">流程别名</param>
        /// <param name="strNotes">申请理由</param>
        /// <returns>返回流程权限ID</returns>
        /// <!--addby zhongjian 20091111-->
        public string SaveRightInfo(string strUserId, string strFlowName,string strFlowType, string strNotes)
        {
            string strNewId = string.Empty;
            int nReturn = 0;
            string strSql = string.Format("select UserId from sys_user_serial t where UserId='{0}' and SERIAL_NAME='{1}'",
                strUserId, strFlowName);
            object objId = dbGloab.ExecuteScalar(CommandType.Text, strSql);
            if (objId == null)
            {
                strSql = string.Format(@"insert into sys_user_serial t(UserId,serial_name,flowtype,notes) 
                    values(:userid,:serialname,:filetype,:notes)  returning id into :newId");

                DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);

                dbGloab.AddInParameter(cmd, "userid", DbType.String, strUserId);
                dbGloab.AddInParameter(cmd, "serialname", DbType.String, strFlowName);
                dbGloab.AddInParameter(cmd, "filetype", DbType.String, strFlowType);
                dbGloab.AddInParameter(cmd, "notes", DbType.String, strNotes);
                dbGloab.AddOutParameter(cmd, "newId", DbType.String, 32);
                nReturn = dbGloab.ExecuteNonQuery(cmd);
                strNewId = dbGloab.GetParameterValue(cmd, "newId").ToString();

                //添加操作日志 addby zhongjian 20100421
                string strRemark = string.Format("新申报办理事项为 {0} 业务,新申请的ID为:{1}", strFlowType, strNewId);
                SystemLogs.AddSystemLogs(strUserId, "add", strRemark, strSql);
            }
            else
            {
                strSql = string.Format(@"update sys_user_serial t set notes='{2}' 
                    where UserId='{0}' and serial_name='{1}'", strUserId, strFlowName, strNotes);
                nReturn=dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
                //获取修改后的ID addby zhongjian 20091112
                strSql = string.Format(@"select id from sys_user_serial where UserId=:userid and serial_name=:serialname");
                DbCommand cmdTemp = dbGloab.GetSqlStringCommand(strSql);
                dbGloab.AddInParameter(cmdTemp, "userid", DbType.String, strUserId);
                dbGloab.AddInParameter(cmdTemp, "serialname", DbType.String, strFlowName);

                DataTable dtTemp = dbGloab.ExecuteDataSet(cmdTemp).Tables[0];
                if (dtTemp.Rows.Count > 0)
                    strNewId = dtTemp.Rows[0][0].ToString();
                else
                    strNewId = nReturn.ToString();

                //添加操作日志
                string strRemark = string.Format("修改申请办理事项为 {0} 业务内容,修改ID为:{1}", strFlowType, strNewId);
                SystemLogs.AddSystemLogs(strUserId, "update", strRemark, strSql);
            }           

            return strNewId;
        }

       

        /// <summary>
        /// 保存流程权限申请上传的附件
        /// </summary>
        /// <param name="fileUpload">上传文件信息包</param>
        /// <param name="dType">存储类型</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091111-->
        public int StoreSerialAtt(FileUploadStruct fileUpload, DataStoreType dType)
        {
            string strSql = string.Empty;
            if (dType == DataStoreType.Insert)
            {
                strSql = string.Format(@"insert into {0}({1},{2},{3},{4}) values(:filedata,
                :fileType,:filename,:serial_id) returning id into :newId", fileUpload.TableName,
                fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreResourceFieldName,
                fileUpload.StoreParamFieldName);
            }
            DbCommand cmd = dbGloab.GetSqlStringCommand(strSql);


            if (dType == DataStoreType.Update)
            {
                dbGloab.AddInParameter(cmd, "keyid", DbType.String, fileUpload.KeyIdValue);
            }

            if (dType == DataStoreType.Insert)
            {
                dbGloab.AddInParameter(cmd, "filedata", DbType.Binary, fileUpload.FileBytes);
                dbGloab.AddInParameter(cmd, "fileType", DbType.String, fileUpload.FileTypeFieldValue);
                dbGloab.AddInParameter(cmd, "filename", DbType.String, fileUpload.StoreResourceNameValue);
                dbGloab.AddInParameter(cmd, "serial_id", DbType.String, fileUpload.StoreParamValue);
            }

            dbGloab.AddOutParameter(cmd, "newId", DbType.String, 32);

            int nReturn = dbGloab.ExecuteNonQuery(cmd);
            string strNewId = dbGloab.GetParameterValue(cmd, "newId").ToString();

            return nReturn;
        }

        /// <summary>
        /// 删除用户权限信息
        /// </summary>
        /// <param name="strId">用户权限对应关系表id</param>
        /// <returns></returns>
        public int DeleteRightInfo(string strId)
        {
            string strSql = @"delete from sys_user_serial where id = :strid";

            DbCommand cmdTemp = dbGloab.GetSqlStringCommand(strSql);
            dbGloab.AddInParameter(cmdTemp, "strid", DbType.String, strId);

            int nReturn=dbGloab.ExecuteNonQuery(cmdTemp);
            if(nReturn>0)
                nReturn=DeleteSerialAtt(strId);
            return nReturn;
        }

        /// <summary>
        /// 存储权限信息
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strFlowName">流程名</param>
        /// <param name="strNotes">备注</param>
        /// <returns></returns>
        public int StoreRightInfo(string strUserId,string strFlowName,string strNotes)
        {
            string strSql = string.Format("select UserId from sys_user_serial t where UserId='{0}' and SERIAL_NAME='{1}'",
                strUserId,strFlowName);
            object objId = dbGloab.ExecuteScalar( CommandType.Text,strSql);

            if (objId==null)
            {
                strSql = string.Format(@"insert into sys_user_serial t(UserId,serial_name,notes) 
                    values('{0}','{1}','{2}')", strUserId,strFlowName,strNotes);
            }
            else
            {
                strSql = string.Format(@"update sys_user_serial t set notes='{2}' 
                    where UserId='{0}' and serial_name='{1}'", strUserId, strFlowName, strNotes);
            }
            return dbGloab.ExecuteNonQuery(CommandType.Text, strSql);
        }

        /// <summary>
        /// 根据用户和办理权限对应表id获取权限信息
        /// </summary>
        /// <param name="strId">用户和办理权限对应表id</param>
        /// <returns></returns>
        public DataTable GetAllRightById(string strId)
        {
            string strSql = @"select * from sys_user_serial where id = upper(:id)";

            DbCommand cmdTemp = dbGloab.GetSqlStringCommand(strSql);
            dbGloab.AddInParameter(cmdTemp, "id", DbType.String, strId);

            DataTable dtTemp = dbGloab.ExecuteDataSet(cmdTemp).Tables[0];
            return dtTemp;
        }

        /// <summary>
        /// 获取流程权限的附件信息
        /// </summary>
        /// <param name="strSerialId">流程权限ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091112-->
        public DataTable GetSerialAtt(string strSerialId)
        {
            string strSql = @"select id,filedata,filetype,filename
                            from sys_user_serialatt where serial_id= upper(:serialid)";

            DbCommand cmdTemp = dbGloab.GetSqlStringCommand(strSql);
            dbGloab.AddInParameter(cmdTemp, "serialid", DbType.String, strSerialId);

            DataTable dtTemp = dbGloab.ExecuteDataSet(cmdTemp).Tables[0];
            return dtTemp;
        }

        /// <summary>
        /// 删除流程权限的附件信息
        /// </summary>
        /// <param name="strSerialId">流程权限ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091112-->
        public int DeleteSerialAtt(string strSerialId)
        {
            string strSql = @"delete from sys_user_serialatt where serial_id = :strid";

            DbCommand cmdTemp = dbGloab.GetSqlStringCommand(strSql);
            dbGloab.AddInParameter(cmdTemp, "strid", DbType.String, strSerialId);

            return dbGloab.ExecuteNonQuery(cmdTemp);
        }

        /// <summary>
        /// 获取所有已发布的流程别名
        /// </summary>
        /// <returns></returns>
        /// <!--addby zhongjian 20100315-->
        public DataTable GetFlowList()
        {
            string strSql = string.Empty;
            strSql = @"select flowtype keyvalue,id keycode from xt_workflow_define where ispub='1' and isdelete='0'";
            return dbGloab.ExecuteDataSet(CommandType.Text, strSql).Tables[0];
        }


        /// <summary>
        /// 获取委托
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public DataTable GetDelegate(string UserId)
        {
            string sql = string.Format(@"SELECT a.Id,
                                               b.user_name username,
                                               c.user_name delegate_UserName,
                                               delegate_task,
                                               delegate_time,
                                               begin_time,
                                               end_time
                                          FROM st_delegate a, st_user b, st_user c
                                         WHERE a.userid = b.userid
                                           and a.delegate_userid = c.userid
                                           AND a.USERID = '{0}'", UserId);
            DataTable dt;
            SysParams.OAConnection().RunSql(sql, out dt);
            return dt;
        }

        /// <summary>
        /// 插入委托
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="ddlUserName"></param>
        /// <param name="ddlProcessName"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public int InserDelegate(string UserId, string ddlUserName, string ddlProcessName, string startDate, string endDate)
        {
            string strSql = "";
            strSql = "INSERT INTO st_delegate(userid,delegate_userid, delegate_task, delegate_time,  begin_time, end_time)"
                    + " VALUES('{0}','{1}','{2}',sysdate,to_date('{3}','YYYY-MM-DD HH24:Mi:SS'),to_date('{4}','YYYY-MM-DD HH24:Mi:SS'))";
            strSql = String.Format(strSql, UserId, ddlUserName, ddlProcessName, startDate, endDate);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 删除委托
        /// </summary>
        /// <param name="id"></param>
        public void DeleteDelegate(string id)
        {
            string sql = "DELETE FROM ST_Delegate WHERE ID='" + id + "'";
            SysParams.OAConnection().RunSql(sql);
        }

        /// <summary>
        /// 查询委托用户
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="strWname"></param>
        /// <returns></returns>
        public string GetDelegateByUserId(string UserId, string strWname)
        {
            string strSql = string.Format("select userid from st_delegate where userid='{0}' and DELEGATE_TASK='{1}'", UserId, strWname);
            string strGetUserId = SysParams.OAConnection().GetValue(strSql);
            return strGetUserId;
        }


        /// <summary>
        /// 查询权限用户
        /// </summary>
        /// <returns></returns>
        public static DataTable GetUserRight()
        {
            string sql = "SELECT a.*,b.user_name username FROM xt_query_right a,st_user b where a.userid=b.userid  ";
            DataTable dt;

            SysParams.OAConnection().RunSql(sql, out dt);
            return dt;
        }
        /// <summary>
        /// 更新用户权限
        /// </summary>
        /// <param name="ddlUserName"></param>
        /// <param name="ddlProcessName"></param>
        public static void UpdateUserRight(string ddlUserName, string ddlProcessName)
        {
            string sql = "INSERT INTO xt_query_right(userid,task)"
            + " VALUES('{0}','{1}')";
            sql = String.Format(sql, ddlUserName, ddlProcessName);
            SysParams.OAConnection().RunSql(sql);

        }
        /// <summary>
        /// 删除用户权限
        /// </summary>
        /// <param name="id"></param>
        public static void DeleteUserRight(string id)
        {
            string sql = "DELETE FROM xt_query_right WHERE ID='" + id + "'";
            SysParams.OAConnection().RunSql(sql);
        }

    }
}
